---
title: PostgreSQL
description: Integrate Better Auth with PostgreSQL.
---

PostgreSQL is a powerful, open-source relational database management system known for its advanced features, extensibility, and support for complex queries and large datasets.
Read more [here](https://www.postgresql.org/).

## Example Usage

Make sure you have PostgreSQL installed and configured.
Then, you can connect it straight into Better Auth.

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    connectionString: "postgres://user:password@localhost:5432/database",
  }),
});
```

<Callout>
  For more information, read Kysely's documentation to the
  [PostgresDialect](https://kysely-org.github.io/kysely-apidoc/classes/PostgresDialect.html).
</Callout>

## Schema generation & migration

The [Better Auth CLI](/docs/concepts/cli) allows you to generate or migrate
your database schema based on your Better Auth configuration and plugins.

<table>
  <thead>
    <tr className="border-b">
      <th>
        <p className="font-bold text-[16px] mb-1">PostgreSQL Schema Generation</p>
      </th>
      <th>
        <p className="font-bold text-[16px] mb-1">PostgreSQL Schema Migration</p>
      </th>
    </tr>
  </thead>
  <tbody>
    <tr className="h-10">
      <td>✅ Supported</td>
      <td>✅ Supported</td>
    </tr>
  </tbody>
</table>

```bash title="Schema Generation"
npx @better-auth/cli@latest generate
```

```bash title="Schema Migration"
npx @better-auth/cli@latest migrate
```

## Use a non-default schema

In most cases, the default schema is `public`. To have Better Auth use a
non-default schema (e.g., `auth`) for its tables, you have several options:

### Option 1: Set search_path in connection string (Recommended)

Append the `options` parameter to your connection URI:

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    connectionString: "postgres://user:password@localhost:5432/database?options=-c search_path=auth",
  }),
});
```

URL-encode if needed: `?options=-c%20search_path%3Dauth`.

### Option 2: Set search_path using Pool options

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "password",
    database: "mydb",
    options: "-c search_path=auth",
  }),
});
```

### Option 3: Set default schema for database user

Set the PostgreSQL user's default schema:

```sql
ALTER USER your_user SET search_path TO auth;
```

After setting this, reconnect to apply the changes.

### Prerequisites

Before using a non-default schema, ensure:

1. **The schema exists:**
   ```sql
   CREATE SCHEMA IF NOT EXISTS auth;
   ```

2. **The user has appropriate permissions:**
   ```sql
   GRANT ALL PRIVILEGES ON SCHEMA auth TO your_user;
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA auth TO your_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON TABLES TO your_user;
   ```

### How it works

The Better Auth CLI migration system automatically detects your configured `search_path`:

- When running `npx @better-auth/cli migrate`, it inspects only the tables in your configured schema
- Tables in other schemas (e.g., `public`) are ignored, preventing conflicts
- All new tables are created in your specified schema

### Troubleshooting

<Callout type="warning">
**Issue:** "relation does not exist" error during migration

**Solution:** This usually means the schema doesn't exist or the user lacks permissions. Create the schema and grant permissions as shown above.
</Callout>

<Callout type="info">
**Verifying your schema configuration:**

You can verify which schema Better Auth will use by checking the `search_path`:

```sql
SHOW search_path;
```

This should return your custom schema (e.g., `auth`) as the first value.
</Callout>

## Additional Information

PostgreSQL is supported under the hood via the [Kysely](https://kysely.dev/) adapter, any database supported by Kysely would also be supported. (<Link href="/docs/adapters/other-relational-databases">Read more here</Link>)

If you're looking for performance improvements or tips, take a look at our guide to <Link href="/docs/guides/optimizing-for-performance">performance optimizations</Link>.

